﻿using ET.DBCommon.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ET.DBCommon.DAL
{
    public class SceneFileDAL
    {
        public static List<SceneFileModel> GetSceneFileList()
        {
            List<SceneFileModel> lst = new List<SceneFileModel>();
            string sql = "select * from scenefile";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            DataTable dt = ds.Tables[0];
            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SceneFileModel model = new SceneFileModel();
                    model.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
                    if (dt.Rows[i]["SceneFile"] != DBNull.Value)
                        model.SceneFile = (byte[])dt.Rows[i]["SceneFile"];
                    model.SceneFileName = dt.Rows[i]["SceneFileName"].ToString();
                    lst.Add(model);
                }
            }
            return lst;
        }
        public static List<SceneFileModel> GetAllSceneFileList()
        {
            List<SceneFileModel> lst = new List<SceneFileModel>();
            string sql = "SELECT Id, SceneFileName, UsedQuestions FROM scenefile;";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            DataTable dt = ds.Tables[0];
            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SceneFileModel model = new SceneFileModel();
                    model.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());

                    //if (dt.Rows[i]["SceneFile"] != DBNull.Value)
                    //    model.SceneFile = (byte[])dt.Rows[i]["SceneFile"];

                    model.SceneFileName = dt.Rows[i]["SceneFileName"].ToString();
                    if (dt.Rows[i]["UsedQuestions"] != DBNull.Value)
                        model.UsedQuestions = dt.Rows[i]["UsedQuestions"].ToString();
                    lst.Add(model);
                }
            }
            return lst;
        }

        public static List<SceneFileModel> GetAllSceneFileListIgnoreFile()
        {
            List<SceneFileModel> lst = new List<SceneFileModel>();
            string sql = "select Id, SceneFileName, UsedQuestions from scenefile ";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            DataTable dt = ds.Tables[0];
            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SceneFileModel model = new SceneFileModel();
                    model.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
  
                    //if (dt.Rows[i]["SceneFile"] != DBNull.Value)
                    //    model.SceneFile = (byte[])dt.Rows[i]["SceneFile"];

                    model.SceneFileName = dt.Rows[i]["SceneFileName"].ToString();
                    if (dt.Rows[i]["UsedQuestions"] != DBNull.Value)
                        model.UsedQuestions = dt.Rows[i]["UsedQuestions"].ToString();
                    lst.Add(model);
                }
            }
            return lst;
        }

        public static void InsertSceneFile(List<SceneFileModel> lst)
        {
            foreach (var model in lst)
            {
                string sql = "insert into scenefile (SceneFile,SceneFileName) values (@SceneFile,@SceneFileName)";
                MySqlParameter[] parms = {
                new MySqlParameter("@SceneFile", model.SceneFile),
                new MySqlParameter("@SceneFileName", model.SceneFileName)
            };  DbHelper.ExecuteNonQuery(sql, parms);
            }
        }
        public static void UpdateSceneFile(SceneFileModel model)
        {
            string sql = "update SceneFile set SceneFile = @SceneFile,SceneFileName=@SceneFileName where Id = @Id";
            MySqlParameter[] parms = {               
                new MySqlParameter("@SceneFile", model.SceneFile),
                new MySqlParameter("@SceneFileName", model.SceneFileName),
                new MySqlParameter("@Id", model.Id)
            };
            DbHelper.ExecuteNonQuery(sql, parms);
        }
        public static void UpdateUsedQuestion(SceneFileModel model)
        {
            string sql = "update SceneFile set UsedQuestions=@UsedQuestions where Id = @Id";
            MySqlParameter[] parms = {
                new MySqlParameter("@UsedQuestions", model.UsedQuestions),
                new MySqlParameter("@Id", model.Id)
            };
            DbHelper.ExecuteNonQuery(sql, parms);
        }
        public static void DeleteSceneFile(SceneFileModel model)
        {
            string sql = "delete from scenefile where Id = @Id";
            MySqlParameter[] parms = {
                new MySqlParameter("@Id", model.Id)
            };
            DbHelper.ExecuteNonQuery(sql, parms);
        }
        public static void DeleteAllSceneFile()
        {
            string sql = "delete  from scenefile";
            DbHelper.ExecuteNonQuery(sql);
        }
    }
}
